const express = require('express');
const { handleError, executeQuery, formatDateTime } = require('../utils');
const router = express.Router();

// 查询期刊列表
router.post('/list', async (req, res) => {
    const { page = 1, pageSize = 10, search = '', location_id = '' } = req.body;
    const offset = (page - 1) * pageSize;
    const limit = pageSize;

    const searchQuery = `%${search}%`;

    // 修改查询条件，加入 location_id 过滤
    let totalQuery = `
        SELECT COUNT(*) AS total
        FROM Journals j
        LEFT JOIN JournalDirectories d ON j.directory_id = d.directory_id
        LEFT JOIN LibraryLocation l ON j.location_id = l.location_id
        WHERE d.journal_name LIKE ?`;

    let query = `
        SELECT
            j.journal_id, j.year, j.volume, j.issue, j.stock,
            d.*,
            l.location_id, l.location_name, l.address, l.description,
            j.created_at, j.updated_at, j.image_url
        FROM Journals j
        LEFT JOIN LibraryLocation l ON j.location_id = l.location_id
        LEFT JOIN JournalDirectories d ON j.directory_id = d.directory_id
        WHERE d.journal_name LIKE ?`;

    // 如果 location_id 被传递过来，加入过滤条件
    if (location_id) {
        totalQuery += ` AND j.location_id = ?`;
        query += ` AND j.location_id = ?`;
    }

    query += ` LIMIT ${limit} OFFSET ${offset}`;

    try {
        // 获取总记录数
        const totalResult = await executeQuery(totalQuery, location_id ? [searchQuery, location_id] : [searchQuery]);
        const total = totalResult[0].total;

        // 获取分页数据
        const journals = await executeQuery(query, location_id ? [searchQuery, location_id] : [searchQuery]);

        // 格式化日期字段
        journals.forEach(journal => {
            journal.created_at = formatDateTime(journal.created_at);
            journal.updated_at = formatDateTime(journal.updated_at);
        });

        res.json({ total, page, pageSize, data: journals });
    } catch (error) {
        handleError(error, res);
    }
});


// 假设服务器地址是 http://localhost:3000
const serverBaseUrl = 'http://localhost:3000';

// 新增期刊
router.post('/add', async (req, res) => {
    const { directory_id, year, volume, issue, location_id, stock, image_url } = req.body;

    // 如果有图片路径，拼接服务器地址
    const fullImageUrl = image_url ? `${serverBaseUrl}${image_url}` : null;

    const query = `
        INSERT INTO Journals (directory_id, year, volume, issue, location_id, stock , image_url, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW());
    `;

    try {
        await executeQuery(query, [directory_id, year, volume, issue , location_id, stock || 0, fullImageUrl]);
        res.json({ message: '期刊添加成功' });
    } catch (error) {
        handleError(error, res);
    }
});

// 更新期刊信息
router.post('/update', async (req, res) => {
    const { journal_id, directory_id, year, volume, issue, location_id, stock, image_url } = req.body;

    // 如果有图片路径，拼接服务器地址
    const fullImageUrl = image_url ? `${serverBaseUrl}${image_url}` : null;

    const query = `
        UPDATE Journals
        SET directory_id = ?, year = ?, volume = ?, issue = ?, location_id = ?, stock = ?, image_url = ?, updated_at = NOW()
        WHERE journal_id = ?;
    `;

    try {
        await executeQuery(query, [directory_id, year, volume, issue, location_id, stock, fullImageUrl, journal_id]);
        res.json({ message: '期刊更新成功' });
    } catch (error) {
        handleError(error, res);
    }
});


// 删除期刊
router.post('/delete', async (req, res) => {
    const { journal_id } = req.body;
    const query = 'DELETE FROM Journals WHERE journal_id = ?;';

    try {
        await executeQuery(query, [journal_id]);
        res.json({ message: '期刊删除成功' });
    } catch (error) {
        handleError(error, res);
    }
});

module.exports = router;
